﻿namespace WpfApplication1
{
    using System;
    using System.Data;
    using System.Windows;
    using System.Windows.Controls;
    using OutlookCalendar.Model;
    using System.Windows.Media;
    using System.Collections.Generic;

    internal class Functionality
    {
        public static string id_filmo = "0";
        public static int countrec = 100;
        public static int fromrec = 0;
        public static string CalendarDayOfWeek = "";
        public static Appointments appointments = new Appointments();
        public static Appointment appointmentSelected = null;

        public static void ArchiveRecord(DataGrid dataGrid1, string searchword)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            for (int i = 0; i < dataGrid1.SelectedItems.Count; i++)
            {
                DataRowView li = (DataRowView) dataGrid1.SelectedItems[i];
                string idd = li.Row.ItemArray.GetValue(0x22).ToString();
               // dbc.sqlExecute("SET IDENTITY_INSERT Archyvas$Archyve ON");
                string sql = "SET IDENTITY_INSERT Archyvas$Archyve ON;insert into Archyvas$Archyve (id,[Kompanija],[Pavadinimas originalo kalba],[Lietuviškas pavadinimas],[Trukmė 1],[Trukmė 2],[TC trukmė],[Pradzios TC],[I Įsikirtimas],[II Įsikirtimas] ,[III Įsikirtimas],[IV Įsikirtimas],[V Įsikirtimas],[VI Įsikirtimas],[VII Įsikirtimas],[VIII Įsikirtimas],[IX Įsikirtimas],[X Įsikirtimas],[XI Įsikirtimas],[Eteris 1],[Eteris 2],[Eteris 3],[Eteris 4],[Eteris 5] ,[P raidė],[Intarpai bėg#eil],[Licencijos pradžia],[Licencijos pabaiga],žanras,Metai,Režisierius,Aktoriai,Sezonas,Serija,[XII Įsikirtimas],[XIII Įsikirtimas],[XIV Įsikirtimas],[XV Įsikirtimas],KasetesNr,Salis,indeksas,Filmo_ID) values (" + idd + ",N'" + li.Row.ItemArray.GetValue(0).ToString() + "',N'" + li.Row.ItemArray.GetValue(1).ToString() + "',N'" + li.Row.ItemArray.GetValue(2).ToString() + "',N'" + li.Row.ItemArray.GetValue(3).ToString() + "',N'" + li.Row.ItemArray.GetValue(4).ToString() + "',N'" + li.Row.ItemArray.GetValue(5).ToString() + "',N'" + li.Row.ItemArray.GetValue(6).ToString() + "',N'" + li.Row.ItemArray.GetValue(7).ToString() + "',N'" + li.Row.ItemArray.GetValue(8).ToString() + "',N'" + li.Row.ItemArray.GetValue(9).ToString() + "',N'" + li.Row.ItemArray.GetValue(10).ToString() + "',N'" + li.Row.ItemArray.GetValue(11).ToString() + "',N'" + li.Row.ItemArray.GetValue(12).ToString() + "',N'" + li.Row.ItemArray.GetValue(13).ToString() + "',N'" + li.Row.ItemArray.GetValue(14).ToString() + "',N'" + li.Row.ItemArray.GetValue(15).ToString() + "',N'" + li.Row.ItemArray.GetValue(16).ToString() + "',N'" + li.Row.ItemArray.GetValue(17).ToString() + "',N'" + li.Row.ItemArray.GetValue(18).ToString() + "',N'" + li.Row.ItemArray.GetValue(19).ToString() + "',N'" + li.Row.ItemArray.GetValue(20).ToString() + "',N'" + li.Row.ItemArray.GetValue(21).ToString() + "',N'" + li.Row.ItemArray.GetValue(22).ToString() + "',N'" + li.Row.ItemArray.GetValue(23).ToString() + "',N'" + li.Row.ItemArray.GetValue(24).ToString() + "',N'" + li.Row.ItemArray.GetValue(26).ToString() + "',N'" + li.Row.ItemArray.GetValue(27).ToString() + "',N'" + li.Row.ItemArray.GetValue(28).ToString() + "',N'" + li.Row.ItemArray.GetValue(29).ToString() + "',N'" + li.Row.ItemArray.GetValue(30).ToString() + "',N'" + li.Row.ItemArray.GetValue(31).ToString() + "',N'" + li.Row.ItemArray.GetValue(32).ToString() + "',N'" + li.Row.ItemArray.GetValue(33).ToString() + "',N'" + li.Row.ItemArray.GetValue(35).ToString() + "',N'" + li.Row.ItemArray.GetValue(36).ToString() + "',N'" + li.Row.ItemArray.GetValue(37).ToString() + "',N'" + li.Row.ItemArray.GetValue(38).ToString() + "',N'" + li.Row.ItemArray.GetValue(39).ToString() + "',N'" + li.Row.ItemArray.GetValue(40).ToString() + "',N'" + li.Row.ItemArray.GetValue(41).ToString() + "',N'" + li.Row.ItemArray.GetValue(42).ToString() + "');SET IDENTITY_INSERT Archyvas$Archyve OFF;";
                dbc.sqlExecute(sql);
             //   dbc.sqlExecute("SET IDENTITY_INSERT Archyvas$Archyve OFF");
                DataTable tbv = dbc.getTable("select type,filename from VideoArch where archid=" + idd);
                string filename = "\\\\192.168.0.105\\videoarchive\\" + tbv.Rows[0][0].ToString() + "\\" + tbv.Rows[0][1].ToString();
                string filename1 = "\\\\192.168.0.105\\videoarchive\\ARCHYVAS\\" + tbv.Rows[0][1].ToString();
                System.IO.File.Move(filename, filename1);
                dbc.sqlExecute("delete from Archyvas$ where id=" + idd);
                MessageBox.Show("Perkelta į archyvą!");
                
            }
            DataTable tb = dbc.FindStringInTableArch(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        public static void DeleteFromDB(DataGrid dataGrid1, string searchword)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            for (int i = 0; i < dataGrid1.SelectedItems.Count; i++)
            {
                DataRowView li = (DataRowView) dataGrid1.SelectedItems[i];
                dbc.sqlExecute("delete from Archyvas$ where id=" + li.Row.ItemArray.GetValue(0x22).ToString());
                dbc.sqlExecute("delete from Archyvas$Archyve where id=" + li.Row.ItemArray.GetValue(0x22).ToString());
                DataTable tbv = dbc.getTable("select type,filename from VideoArch where archid=" + li.Row.ItemArray.GetValue(0x22).ToString());
                if (tbv.Rows.Count > 0)
                {
                    dbc.sqlExecute("delete from VideoArch where archid=" + li.Row.ItemArray.GetValue(0x22).ToString());
                    string filename = "\\\\192.168.0.105\\videoarchive\\" + tbv.Rows[0][0].ToString() + "\\" + tbv.Rows[0][1].ToString();
                    System.IO.File.Delete(filename);
                    string filenamePrev = "\\\\192.168.0.105\\videoarchive\\Preview\\" + tbv.Rows[0][1].ToString().Replace(".mpg",".mp4");
                    System.IO.File.Delete(filenamePrev);
                    dbc.sqlExecute("delete from VideoArch where archid=" + li.Row.ItemArray.GetValue(0x22).ToString());
                }
                MessageBox.Show("Įrašas ištrintas!");
               /* DataTable tb = dbc.getTable("select Folders from Config where Id=1");
                string[] fl = tb.Rows[0][0].ToString().Split('|');
                foreach (string st in fl)
                {
                }*/
            }
            DataTable tb = dbc.FindStringInTable(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        public static void getData(DataGrid dataGrid1)
        {
            fromrec = 0;
            countrec = 100;
            DataTable tb = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem").getTable("select * from Archyvas$ order by Kompanija,[Pavadinimas originalo kalba] asc");
            dataGrid1.DataContext = tb;
        }

        public static void getDataArch(DataGrid dataGrid1)
        {
            fromrec = 0;
            countrec = 100;
            DataTable tb = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem").getTable("select top 50 * from Archyvas$Archyve order by Kompanija,[Pavadinimas originalo kalba] asc");
            dataGrid1.DataContext = tb;
        }

        public static bool insertIntoDB(Window2 iw)
        {
            bool commited = false;
            string sql;
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            if (!iw.textBox33.Text.Equals(""))
            {
                if (int.Parse(iw.textBox33.Text) > 0)
                {
                    for (int i = 0; i < int.Parse(iw.textBox33.Text); i++)
                    {
                        string originname = "";
                        if (iw.textBox2.Text.Contains("''"))
                        {
                            originname = iw.textBox2.Text;
                        }
                        else { originname = iw.textBox2.Text.Replace("'", "''"); }
                        string sq = "select * from Archyvas$ where Sezonas='" + iw.textBox32.Text + "' and Serija='" + (i + 1) + "' and [Pavadinimas originalo kalba] Like '%" + originname + "%'";
                        if (dbc.getTable(sq).Rows.Count == 0)
                        {
                            string sqll = "";
                            sqll = sqll + "insert into Archyvas$ ([Kompanija],[Pavadinimas originalo kalba],[Lietuviškas pavadinimas],[Trukmė 1],[Trukmė 2],[TC trukmė],[Pradzios TC],[I Įsikirtimas],[II Įsikirtimas] ,[III Įsikirtimas],[IV Įsikirtimas],[V Įsikirtimas],[VI Įsikirtimas],[VII Įsikirtimas],[VIII Įsikirtimas],[IX Įsikirtimas],[X Įsikirtimas],[XI Įsikirtimas],[Eteris 1],[Eteris 2],[Eteris 3],[Eteris 4],[Eteris 5] ,[P raidė],[Intarpai bėg#eil],[Licencijos pradžia],[Licencijos pabaiga],žanras,Metai,Režisierius,Aktoriai,Sezonas,Serija,[XII Įsikirtimas],[XIII Įsikirtimas],[XIV Įsikirtimas],[XV Įsikirtimas],KasetesNr,Salis,indeksas,Filmo_ID) values (N'";
                            string kompanija = "";
                            if (iw.textBox1.Text.Contains("''"))
                            {
                                kompanija = iw.textBox1.Text.Replace("'", "''");
                            }
                            else { kompanija = iw.textBox1.Text.Replace("'", "''"); }

                            sqll = sqll + kompanija;
                            sqll = sqll + "',N'";
                            
                            sqll = sqll + originname;
                            sqll = sqll + " ";
                            if (!iw.textBox32.Text.Equals(""))
                            {
                                sqll = sqll + iw.textBox32.Text.Replace("'", "''");
                                sqll = sqll + "/";
                            }
                            sqll = sqll + (i + 1).ToString();
                            sqll = sqll + "',N'";

                            string name = "";
                            if (iw.textBox3.Text.Contains("''"))
                            {
                                name = iw.textBox3.Text.Replace("'", "''");
                            }
                            else { name = iw.textBox3.Text.Replace("'", "''"); }
                            sqll = sqll + name;
                            sqll = sqll + " ";
                            if (!iw.textBox32.Text.Equals(""))
                            {
                                sqll = sqll + iw.textBox32.Text.Replace("'", "''");
                                sqll = sqll + "/";
                            }
                            sqll = sqll + (i + 1).ToString();
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox4.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox5.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox6.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox7.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox8.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox9.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox10.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox11.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox12.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox13.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox14.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox15.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox16.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox17.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox18.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox19.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox20.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox21.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox22.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox23.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox24.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox25.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox26.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox27.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox28.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox29.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox30.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox31.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox32.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + (i + 1).ToString();
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox34.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox35.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox36.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox37.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox38.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox39.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox40.Text.Replace("'", "''");
                            sqll = sqll + "',N'";
                            sqll = sqll + iw.textBox42.Text.Replace("'", "''");
                            sqll = sqll + "')";
                            commited = dbc.sqlExecute(sqll);
                        }
                    }
                }
            }
            else
            {
                sql = "insert into Archyvas$ ([Kompanija],[Pavadinimas originalo kalba],[Lietuviškas pavadinimas],[Trukmė 1],[Trukmė 2],[TC trukmė],[Pradzios TC],[I Įsikirtimas],[II Įsikirtimas] ,[III Įsikirtimas],[IV Įsikirtimas],[V Įsikirtimas],[VI Įsikirtimas],[VII Įsikirtimas],[VIII Įsikirtimas],[IX Įsikirtimas],[X Įsikirtimas],[XI Įsikirtimas],[Eteris 1],[Eteris 2],[Eteris 3],[Eteris 4],[Eteris 5] ,[P raidė],[Intarpai bėg#eil],[Licencijos pradžia],[Licencijos pabaiga],žanras,Metai,Režisierius,Aktoriai,Sezonas,Serija,[XII Įsikirtimas],[XIII Įsikirtimas],[XIV Įsikirtimas],[XV Įsikirtimas],KasetesNr,Salis,indeksas,Filmo_ID) values (N'" + iw.textBox1.Text.Replace("'", "''") + "',N'" + iw.textBox2.Text.Replace("'", "''") + "',N'" + iw.textBox3.Text.Replace("'", "''") + "',N'" + iw.textBox4.Text.Replace("'", "''") + "',N'" + iw.textBox5.Text.Replace("'", "''") + "',N'" + iw.textBox6.Text.Replace("'", "''") + "',N'" + iw.textBox7.Text.Replace("'", "''") + "',N'" + iw.textBox8.Text.Replace("'", "''") + "',N'" + iw.textBox9.Text.Replace("'", "''") + "',N'" + iw.textBox10.Text.Replace("'", "''") + "',N'" + iw.textBox11.Text.Replace("'", "''") + "',N'" + iw.textBox12.Text.Replace("'", "''") + "',N'" + iw.textBox13.Text.Replace("'", "''") + "',N'" + iw.textBox14.Text.Replace("'", "''") + "',N'" + iw.textBox15.Text.Replace("'", "''") + "',N'" + iw.textBox16.Text.Replace("'", "''") + "',N'" + iw.textBox17.Text.Replace("'", "''") + "',N'" + iw.textBox18.Text.Replace("'", "''") + "',N'" + iw.textBox19.Text.Replace("'", "''") + "',N'" + iw.textBox20.Text.Replace("'", "''") + "',N'" + iw.textBox21.Text.Replace("'", "''") + "',N'" + iw.textBox22.Text.Replace("'", "''") + "',N'" + iw.textBox23.Text.Replace("'", "''") + "',N'" + iw.textBox24.Text.Replace("'", "''") + "',N'" + iw.textBox25.Text.Replace("'", "''") + "',N'" + iw.textBox26.Text.Replace("'", "''") + "',N'" + iw.textBox27.Text.Replace("'", "''") + "',N'" + iw.textBox28.Text.Replace("'", "''") + "',N'" + iw.textBox29.Text.Replace("'", "''") + "',N'" + iw.textBox30.Text.Replace("'", "''") + "',N'" + iw.textBox31.Text.Replace("'", "''") + "',N'" + iw.textBox32.Text.Replace("'", "''") + "',N'" + iw.textBox33.Text.Replace("'", "''") + "',N'" + iw.textBox34.Text.Replace("'", "''") + "',N'" + iw.textBox35.Text.Replace("'", "''") + "',N'" + iw.textBox36.Text.Replace("'", "''") + "',N'" + iw.textBox37.Text.Replace("'", "''") + "',N'" + iw.textBox38.Text.Replace("'", "''") + "',N'" + iw.textBox39.Text.Replace("'", "''") + "',N'" + iw.textBox40.Text.Replace("'", "''") + "',N'" + iw.textBox42.Text.Replace("'", "''") + "')";
                commited=dbc.sqlExecute(sql);
            }
            return commited;
        }

        public static string getCounter()
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            dbc.sqlExecute("update config set Counter=Counter+1 where Id=1");
            DataTable tb = dbc.getTable("select Counter from config where Id=1");
            return tb.Rows[0][0].ToString();
        }

        public static void RestorefromArchive(DataGrid dataGrid1, string searchword)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            for (int i = 0; i < dataGrid1.SelectedItems.Count; i++)
            {
                DataRowView li = (DataRowView) dataGrid1.SelectedItems[i];
                string idd = li.Row.ItemArray.GetValue(0x22).ToString();
              //  dbc.sqlExecute("SET IDENTITY_INSERT Archyvas$ ON");
                string sql = "SET IDENTITY_INSERT Archyvas$ ON;insert into Archyvas$ (id,[Kompanija],[Pavadinimas originalo kalba],[Lietuviškas pavadinimas],[Trukmė 1],[Trukmė 2],[TC trukmė],[Pradzios TC],[I Įsikirtimas],[II Įsikirtimas] ,[III Įsikirtimas],[IV Įsikirtimas],[V Įsikirtimas],[VI Įsikirtimas],[VII Įsikirtimas],[VIII Įsikirtimas],[IX Įsikirtimas],[X Įsikirtimas],[XI Įsikirtimas],[Eteris 1],[Eteris 2],[Eteris 3],[Eteris 4],[Eteris 5] ,[P raidė],[Intarpai bėg#eil],[Licencijos pradžia],[Licencijos pabaiga],žanras,Metai,Režisierius,Aktoriai,Sezonas,Serija,[XII Įsikirtimas],[XIII Įsikirtimas],[XIV Įsikirtimas],[XV Įsikirtimas],KasetesNr,Salis,indeksas,Filmo_ID) values (" + idd + ",N'" + li.Row.ItemArray.GetValue(0).ToString() + "',N'" + li.Row.ItemArray.GetValue(1).ToString() + "',N'" + li.Row.ItemArray.GetValue(2).ToString() + "',N'" + li.Row.ItemArray.GetValue(3).ToString() + "',N'" + li.Row.ItemArray.GetValue(4).ToString() + "',N'" + li.Row.ItemArray.GetValue(5).ToString() + "',N'" + li.Row.ItemArray.GetValue(6).ToString() + "',N'" + li.Row.ItemArray.GetValue(7).ToString() + "',N'" + li.Row.ItemArray.GetValue(8).ToString() + "',N'" + li.Row.ItemArray.GetValue(9).ToString() + "',N'" + li.Row.ItemArray.GetValue(10).ToString() + "',N'" + li.Row.ItemArray.GetValue(11).ToString() + "',N'" + li.Row.ItemArray.GetValue(12).ToString() + "',N'" + li.Row.ItemArray.GetValue(13).ToString() + "',N'" + li.Row.ItemArray.GetValue(14).ToString() + "',N'" + li.Row.ItemArray.GetValue(15).ToString() + "',N'" + li.Row.ItemArray.GetValue(16).ToString() + "',N'" + li.Row.ItemArray.GetValue(17).ToString() + "',N'" + li.Row.ItemArray.GetValue(18).ToString() + "',N'" + li.Row.ItemArray.GetValue(19).ToString() + "',N'" + li.Row.ItemArray.GetValue(20).ToString() + "',N'" + li.Row.ItemArray.GetValue(21).ToString() + "',N'" + li.Row.ItemArray.GetValue(22).ToString() + "',N'" + li.Row.ItemArray.GetValue(23).ToString() + "',N'" + li.Row.ItemArray.GetValue(24).ToString() + "',N'" + li.Row.ItemArray.GetValue(26).ToString() + "',N'" + li.Row.ItemArray.GetValue(27).ToString() + "',N'" + li.Row.ItemArray.GetValue(28).ToString() + "',N'" + li.Row.ItemArray.GetValue(29).ToString() + "',N'" + li.Row.ItemArray.GetValue(30).ToString() + "',N'" + li.Row.ItemArray.GetValue(31).ToString() + "',N'" + li.Row.ItemArray.GetValue(32).ToString() + "',N'" + li.Row.ItemArray.GetValue(33).ToString() + "',N'" + li.Row.ItemArray.GetValue(35).ToString() + "',N'" + li.Row.ItemArray.GetValue(36).ToString() + "',N'" + li.Row.ItemArray.GetValue(37).ToString() + "',N'" + li.Row.ItemArray.GetValue(38).ToString() + "',N'" + li.Row.ItemArray.GetValue(39).ToString() + "',N'" + li.Row.ItemArray.GetValue(40).ToString() + "',N'" + li.Row.ItemArray.GetValue(41).ToString() + "',N'" + li.Row.ItemArray.GetValue(42).ToString() + "');SET IDENTITY_INSERT Archyvas$ OFF;";
                dbc.sqlExecute(sql);
               // dbc.sqlExecute("SET IDENTITY_INSERT Archyvas$ OFF");
                dbc.sqlExecute("delete from Archyvas$Archyve  where id=" + idd);
                DataTable tbv = dbc.getTable("select type,filename from VideoArch where archid=" + idd);
                if (tbv.Rows.Count > 0)
                {
                    string filename = "\\\\192.168.0.105\\videoarchive\\" + tbv.Rows[0][0].ToString() + "\\" + tbv.Rows[0][1].ToString();
                    string filename1 = "\\\\192.168.0.105\\videoarchive\\ARCHYVAS\\" + tbv.Rows[0][1].ToString();
                    System.IO.File.Move(filename1, filename);
                }
                MessageBox.Show("Perkelta iš archyvą!");
            }
            DataTable tb = dbc.FindStringInTableArch(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        public static void SearchInDB(DataGrid dataGrid1, string searchword)
        {
            DataTable tb = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem").FindStringInTable(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        public static void SearchInDBArch(DataGrid dataGrid1, string searchword)
        {
            DataTable tb = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem").FindStringInTableArch(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        internal static void updateIntoDB(Window2 iw, DataGrid dataGrid1, string searchword, string id)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            DataTable tb = dbc.getTable("select * from Archyvas$ where id=" + id);
            iw.Title = "Įrašo redagavimas";
            iw.textBox1.Text = tb.Rows[0]["Kompanija"].ToString();
            iw.textBox2.Text = tb.Rows[0]["Pavadinimas originalo kalba"].ToString();
            iw.textBox3.Text = tb.Rows[0]["Lietuviškas pavadinimas"].ToString();
            iw.textBox4.Text = tb.Rows[0]["Trukmė 1"].ToString();
            iw.textBox5.Text = tb.Rows[0]["Trukmė 2"].ToString();
            iw.textBox6.Text = tb.Rows[0]["TC trukmė"].ToString();
            iw.textBox7.Text = tb.Rows[0]["Pradzios TC"].ToString();
            iw.textBox8.Text = tb.Rows[0]["I Įsikirtimas"].ToString();
            iw.textBox9.Text = tb.Rows[0]["II Įsikirtimas"].ToString();
            iw.textBox10.Text = tb.Rows[0]["III Įsikirtimas"].ToString();
            iw.textBox11.Text = tb.Rows[0]["IV Įsikirtimas"].ToString();
            iw.textBox12.Text = tb.Rows[0]["V Įsikirtimas"].ToString();
            iw.textBox13.Text = tb.Rows[0]["VI Įsikirtimas"].ToString();
            iw.textBox14.Text = tb.Rows[0]["VII Įsikirtimas"].ToString();
            iw.textBox15.Text = tb.Rows[0]["VIII Įsikirtimas"].ToString();
            iw.textBox16.Text = tb.Rows[0]["IX Įsikirtimas"].ToString();
            iw.textBox17.Text = tb.Rows[0]["X Įsikirtimas"].ToString();
            iw.textBox18.Text = tb.Rows[0]["XI Įsikirtimas"].ToString();
            iw.textBox19.Text = tb.Rows[0]["Eteris 1"].ToString();
            iw.textBox20.Text = tb.Rows[0]["Eteris 2"].ToString();
            iw.textBox21.Text = tb.Rows[0]["Eteris 3"].ToString();
            iw.textBox22.Text = tb.Rows[0]["Eteris 4"].ToString();
            iw.textBox23.Text = tb.Rows[0]["Eteris 5"].ToString();
            iw.textBox24.Text = tb.Rows[0]["P raidė"].ToString();
            iw.textBox25.Text = tb.Rows[0]["Intarpai bėg#eil"].ToString();
            iw.textBox26.Text = tb.Rows[0]["Licencijos pradžia"].ToString();
            iw.textBox27.Text = tb.Rows[0]["Licencijos pabaiga"].ToString();
            iw.textBox28.Text = tb.Rows[0]["žanras"].ToString();
            iw.textBox29.Text = tb.Rows[0]["Metai"].ToString();
            iw.textBox30.Text = tb.Rows[0]["Režisierius"].ToString();
            iw.textBox31.Text = tb.Rows[0]["Aktoriai"].ToString();
            iw.textBox32.Text = tb.Rows[0]["Sezonas"].ToString();
            iw.textBox33.Text = tb.Rows[0]["Serija"].ToString();
            iw.textBox34.Text = tb.Rows[0]["XII Įsikirtimas"].ToString();
            iw.textBox35.Text = tb.Rows[0]["XIII Įsikirtimas"].ToString();
            iw.textBox36.Text = tb.Rows[0]["XIV Įsikirtimas"].ToString();
            iw.textBox37.Text = tb.Rows[0]["XV Įsikirtimas"].ToString();
            iw.textBox38.Text = tb.Rows[0]["KasetesNr"].ToString();
            iw.textBox39.Text = tb.Rows[0]["Salis"].ToString();
            iw.textBox40.Text = tb.Rows[0]["indeksas"].ToString();
            iw.textBox42.Text = tb.Rows[0]["Filmo_ID"].ToString();

            if (tb.Rows[0]["Filmo_ID"].ToString().Equals("")) { id_filmo = getCounter(); iw.textBox42.Text = "V_" + id_filmo; }
            else
            {
                id_filmo = tb.Rows[0]["Filmo_ID"].ToString().Replace("V_", "");
                id_filmo = tb.Rows[0]["Filmo_ID"].ToString().Replace("VT_", "");
            }

            if (iw.ShowDialog() == true)
            {
                if (iw.textBox2.Text.Equals(""))
                {
                    MessageBox.Show("Įveskite pavadinimà originalo kalba!");
                }
                else
                {
                    string kompanija = "";
                    if (iw.textBox1.Text.Contains("''"))
                    {
                        kompanija = iw.textBox1.Text;
                    }
                    else { kompanija = iw.textBox1.Text.Replace("'", "''"); }
                    string originname = "";
                    if (iw.textBox2.Text.Contains("''"))
                    {
                        originname = iw.textBox2.Text;
                    }
                    else { originname = iw.textBox2.Text.Replace("'", "''"); }
                    string name = "";
                    if (iw.textBox3.Text.Contains("''"))
                    {
                        name = iw.textBox3.Text;
                    }
                    else { name = iw.textBox3.Text.Replace("'", "''"); }
                    string rezisierius = "";
                    if (iw.textBox30.Text.Contains("''"))
                    {
                        rezisierius = iw.textBox30.Text;
                    }
                    else { rezisierius = iw.textBox30.Text.Replace("'", "''"); }
                    string aktoriai = "";
                    if (iw.textBox31.Text.Contains("''"))
                    {
                        aktoriai = iw.textBox31.Text;
                    }
                    else { aktoriai = iw.textBox31.Text.Replace("'", "''"); }
                    string sql = "UPDATE Archyvas$ SET [Kompanija] = N'" + kompanija + "',[Pavadinimas originalo kalba] = N'" + originname + "',[Lietuviškas pavadinimas] = N'" + name + "',[Trukmė 1] = N'" + iw.textBox4.Text + "',[Trukmė 2] = N'" + iw.textBox5.Text + "',[TC trukmė] = N'" + iw.textBox6.Text + "',[Pradzios TC] = N'" + iw.textBox7.Text + "',[I Įsikirtimas] = N'" + iw.textBox8.Text + "',[II Įsikirtimas] = N'" + iw.textBox9.Text + "',[III Įsikirtimas] = N'" + iw.textBox10.Text + "',[IV Įsikirtimas] = N'" + iw.textBox11.Text + "',[V Įsikirtimas] = N'" + iw.textBox12.Text + "',[VI Įsikirtimas] = N'" + iw.textBox13.Text + "',[VII Įsikirtimas] = N'" + iw.textBox14.Text + "',[VIII Įsikirtimas] = N'" + iw.textBox15.Text + "',[IX Įsikirtimas] = N'" + iw.textBox16.Text + "',[X Įsikirtimas] = N'" + iw.textBox17.Text + "',[XI Įsikirtimas] = N'" + iw.textBox18.Text + "',[Eteris 1] = N'" + iw.textBox19.Text + "',[Eteris 2] = N'" + iw.textBox20.Text + "',[Eteris 3] = N'" + iw.textBox21.Text + "',[Eteris 4] = N'" + iw.textBox22.Text + "',[Eteris 5] = N'" + iw.textBox23.Text + "',[P raidė] = N'" + iw.textBox24.Text + "',[Intarpai bėg#eil] = N'" + iw.textBox25.Text + "',[Licencijos pradžia] = N'" + iw.textBox26.Text + "',[Licencijos pabaiga] = N'" + iw.textBox27.Text + "',žanras=N'" + iw.textBox28.Text + "',Metai=N'" + iw.textBox29.Text + "',Režisierius=N'" + rezisierius + "',Aktoriai=N'" + aktoriai + "',Sezonas=N'" + iw.textBox32.Text + "',Serija=N'" + iw.textBox33.Text + "',[XII Įsikirtimas] = N'" + iw.textBox34.Text + "',[XIII Įsikirtimas] = N'" + iw.textBox35.Text + "',[XIV Įsikirtimas] = N'" + iw.textBox36.Text + "',[XV Įsikirtimas] = N'" + iw.textBox37.Text + "',[KasetesNr] = N'" + iw.textBox38.Text + "', Salis = N'" + iw.textBox39.Text + "', indeksas = '" + iw.textBox40.Text + "', Filmo_id = '" + iw.textBox42.Text + "' WHERE id=" + id;
                    dbc.sqlExecute(sql);
                    tb = dbc.FindStringInTable(searchword, fromrec, countrec);
                    dataGrid1.DataContext = tb;
                }
            }
        }

        public static void LoadAppointmentsByDate(DateTime StartDate, DateTime EndDate)
        {
            EndDate += TimeSpan.FromDays(1);
            EndDate = new DateTime(EndDate.Year, EndDate.Month, EndDate.Day, 0, 0, 0);
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            DataTable tb = dbc.getTable("select * from Programa where StartDate>='" + StartDate + "' and EndDate<'" + EndDate + "' order by StartDate");
            appointments.Clear();
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                Appointment ap = new Appointment();
                ap.Subject=tb.Rows[i]["Subject"].ToString();
                ap.StartTime=(DateTime)tb.Rows[i]["StartDate"];
                ap.EndTime = (DateTime)tb.Rows[i]["EndDate"];
                ap.Location = tb.Rows[i]["subID"].ToString();
                appointments.Add(ap);
            }
            try
                {
                    for (int y = 0; y <= appointments.Count; y++)
                    {
                        for (int i = appointments.Count-1; i >= 0; i--)
                        {
                            try
                            {
                                Appointment lastAp = appointments[i];
                                Appointment beforelastAp = appointments[i+1];
                                if ((lastAp.StartTime.Hour < 6) && (beforelastAp.StartTime.Hour >= 6) && lastAp.StartTime.ToShortDateString().Equals(beforelastAp.StartTime.ToShortDateString()))
                                {
                                    Appointment tmp = appointments[i];
                                    appointments[i] = appointments[i + 1];
                                    appointments[i + 1] = tmp;
                                }
                            }
                            catch (Exception ex) { }

                        }
                    }
                }
            catch (Exception ex) { }
        }

        public static void addAppointmentToDB(Appointment ap)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            string sql = "insert into Programa (Subject,StartDate,EndDate,subID) values (N'" + ap.Subject.Replace("\r\n", "") + "','" + ap.StartTime + "','" + ap.EndTime + "','" + ap.Location + "')";
            dbc.sqlExecute(sql);
        }
        public static void removeAppointmentFromDB(Appointment ap)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            string sub = ap.Subject.Replace("\r\n", "");
            string sqlc = "delete from Programa where StartDate='" + ap.StartTime + "' and EndDate='" + ap.EndTime + "'";
           // string sqlc = "delete from Programa where Subject='" + sub + "' and StartDate='" + ap.StartTime + "' and EndDate='" + ap.EndTime + "'";
            dbc.sqlExecute(sqlc);
        }

        public static DateTime GetFirstDateOfWeek(DateTime dayInWeek, DayOfWeek firstDay)
        {
            DateTime firstDayInWeek = dayInWeek.Date;
            while (firstDayInWeek.DayOfWeek != firstDay)
                firstDayInWeek = firstDayInWeek.AddDays(-1);

            return firstDayInWeek;
        }
        public static DateTime GetLastDateOfWeek(DateTime dayInWeek, DayOfWeek firstDay)
        {
            DateTime lastDayInWeek = dayInWeek.Date;
            while (lastDayInWeek.DayOfWeek != firstDay)
                lastDayInWeek = lastDayInWeek.AddDays(1);

            lastDayInWeek -= TimeSpan.FromDays(1);
            return lastDayInWeek;
        }

        public static DataTable getDataFromArchToCombo()
        {
             DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
             DataTable tb = dbc.getTable("select  [Pavadinimas originalo kalba],[Lietuviškas pavadinimas],id,[Trukmė 1] from Archyvas$ where ([Pavadinimas originalo kalba] is not NULL and [Lietuviškas pavadinimas] is not NULL) and [Lietuviškas pavadinimas] <>'' order by dbo.fFilterNumeric([Lietuviškas pavadinimas])");
            return tb;
        }

        public static string removeNr(string str)
        {
            string rez =str;
            int ln = str.Length - 1;
            int cnt = 0;
            for (int i = ln; i > 0; i--)
            {
                try{
                    if (str.Substring(i, 1).Equals("/"))
                    {
                        rez = str.Substring(0, i);
                        ln--;
                        cnt++;
                    }else if ((int.Parse(str.Substring(i, 1)) >= 0) && (int.Parse(str.Substring(i, 1)) <= 9))
                    {
                    rez = str.Substring(0, i);
                    ln--;
                    cnt++;
                }
                }catch(Exception ex){ break;}
            }
            if (cnt>0){
                rez = rez.Substring(0,rez.Length - 1);
            }
            return rez;
        }

        public static string getProgramDateFromDB(string id,string Subject)
        {
            string result = "";
            try
            {
                if (!id.Equals("") && !id.Equals("null"))
                {
                    DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
                    DataTable tb = dbc.getTable("select * from Archyvas$ where id=" + id);

                    string priedas = "";
                    if (Subject.Contains("(tęs.)"))
                    {
                        priedas = " (tęs.)";
                    }
                    if (Subject.Contains("(kart.)"))
                    {
                        priedas = " (kart.)";
                    }
                    if (!tb.Rows[0]["Žanras"].ToString().Equals(""))
                    {
                        if (tb.Rows[0]["Lietuviškas pavadinimas"].ToString().Equals(tb.Rows[0]["Pavadinimas originalo kalba"].ToString()))
                        {
                            result += " " + removeNr(tb.Rows[0]["Lietuviškas pavadinimas"].ToString()) + priedas + ". " + tb.Rows[0]["Žanras"].ToString();
                        }
                        else
                        {
                            string skirt = ".";
                            if (tb.Rows[0]["Žanras"].ToString().Substring(tb.Rows[0]["Žanras"].ToString().Length - 1).Contains("."))
                                skirt = "";
                            string pav = removeNr(tb.Rows[0]["Lietuviškas pavadinimas"].ToString());
                            if (pav.Contains("("))
                            {
                                pav = pav.Replace(" (", "“ (");
                            }
                            else
                            {
                                pav = pav + "“";
                            }
                            result += " " + "„" + pav + priedas + ". " + tb.Rows[0]["Žanras"].ToString() + skirt;

                        }
                    }
                    else
                    {
                        result += " " + removeNr(tb.Rows[0]["Lietuviškas pavadinimas"].ToString()) + priedas;
                    }
                    if (!tb.Rows[0]["Režisierius"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["Režisierius"].ToString();
                    }
                    if (!tb.Rows[0]["Aktoriai"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["Aktoriai"].ToString();
                    }
                    if (!tb.Rows[0]["Salis"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["Salis"].ToString();
                    }
                    if (!tb.Rows[0]["Metai"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["Metai"].ToString();
                    }
                    if (!tb.Rows[0]["indeksas"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["indeksas"].ToString().Replace("(", "").Replace(")", "");
                    }
                    result = result.Replace("..", ".");
                }
            }
            catch (Exception ex) { }
            return result;
        }


        public static string getTinklelismDateFromDB(string id,string Subject)
        {
            string result = "";
            if (!id.Equals("") && !id.Equals("null"))
            {
                string priedas = "";
                if (Subject.Contains("(tęs.)"))
                {
                    priedas = " (tęs.)";
                }
                if (Subject.Contains("(kart.)"))
                {
                    priedas = " (kart.)";
                }
                DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
                DataTable tb = dbc.getTable("select * from Archyvas$ where id=" + id);

                if (tb.Rows.Count > 0)
                {

                    if (!tb.Rows[0]["Lietuviškas pavadinimas"].ToString().Equals(tb.Rows[0]["Pavadinimas originalo kalba"].ToString()))
                    {
                        result = tb.Rows[0]["Lietuviškas pavadinimas"].ToString() + priedas + ".   " + tb.Rows[0]["Pavadinimas originalo kalba"].ToString() + ". " + tb.Rows[0]["Kompanija"].ToString();
                    }
                    else
                    {
                        result = tb.Rows[0]["Pavadinimas originalo kalba"].ToString() + priedas;
                    }
                }
                
                
            }
            return result;
        }

        public static IEnumerable<T> FindVisualChildren<T>(DependencyObject depObj) where T : DependencyObject
        {
            if (depObj != null)
            {
                for (int i = 0; i < VisualTreeHelper.GetChildrenCount(depObj); i++)
                {
                    DependencyObject child = VisualTreeHelper.GetChild(depObj, i);
                    if (child != null && child is T)
                    {
                        yield return (T)child;
                    }

                    foreach (T childOfChild in FindVisualChildren<T>(child))
                    {
                        yield return childOfChild;
                    }
                }
            }
        }
    
    
    }
}


/*namespace WpfApplication1
{
    using System;
    using System.Data;
    using System.Windows;
    using System.Windows.Controls;
    using OutlookCalendar.Model;
    using System.Windows.Media;
    using System.Collections.Generic;

    internal class Functionality
    {
        public static int countrec = 100;
        public static int fromrec = 0;
        public static string CalendarDayOfWeek = "";
        public static Appointments appointments = new Appointments();
        public static Appointment appointmentSelected = null;

        public static void ArchiveRecord(DataGrid dataGrid1, string searchword)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            for (int i = 0; i < dataGrid1.SelectedItems.Count; i++)
            {
                DataRowView li = (DataRowView) dataGrid1.SelectedItems[i];
                string idd = li.Row.ItemArray.GetValue(0x22).ToString();
                string sql = "insert into Archyvas$Archyve ([Kompanija],[Pavadinimas originalo kalba],[Lietuviškas pavadinimas],[Trukmė 1],[Trukmė 2],[TC trukmė],[Pradzios TC],[I Įsikirtimas],[II Įsikirtimas] ,[III Įsikirtimas],[IV Įsikirtimas],[V Įsikirtimas],[VI Įsikirtimas],[VII Įsikirtimas],[VIII Įsikirtimas],[IX Įsikirtimas],[X Įsikirtimas],[XI Įsikirtimas],[Eteris 1],[Eteris 2],[Eteris 3],[Eteris 4],[Eteris 5] ,[P raidė],[Intarpai bėg#eil],[Licencijos pradžia],[Licencijos pabaiga],žanras,Metai,Režisierius,Aktoriai,Sezonas,Serija,[XII Įsikirtimas],[XIII Įsikirtimas],[XIV Įsikirtimas],[XV Įsikirtimas],KasetesNr,Salis,indeksas) values ('" + li.Row.ItemArray.GetValue(0).ToString() + "','" + li.Row.ItemArray.GetValue(1).ToString() + "','" + li.Row.ItemArray.GetValue(2).ToString() + "','" + li.Row.ItemArray.GetValue(3).ToString() + "','" + li.Row.ItemArray.GetValue(4).ToString() + "','" + li.Row.ItemArray.GetValue(5).ToString() + "','" + li.Row.ItemArray.GetValue(6).ToString() + "','" + li.Row.ItemArray.GetValue(7).ToString() + "','" + li.Row.ItemArray.GetValue(8).ToString() + "','" + li.Row.ItemArray.GetValue(9).ToString() + "','" + li.Row.ItemArray.GetValue(10).ToString() + "','" + li.Row.ItemArray.GetValue(11).ToString() + "','" + li.Row.ItemArray.GetValue(12).ToString() + "','" + li.Row.ItemArray.GetValue(13).ToString() + "','" + li.Row.ItemArray.GetValue(14).ToString() + "','" + li.Row.ItemArray.GetValue(15).ToString() + "','" + li.Row.ItemArray.GetValue(16).ToString() + "','" + li.Row.ItemArray.GetValue(17).ToString() + "','" + li.Row.ItemArray.GetValue(18).ToString() + "','" + li.Row.ItemArray.GetValue(19).ToString() + "','" + li.Row.ItemArray.GetValue(20).ToString() + "','" + li.Row.ItemArray.GetValue(21).ToString() + "','" + li.Row.ItemArray.GetValue(22).ToString() + "','" + li.Row.ItemArray.GetValue(23).ToString() + "','" + li.Row.ItemArray.GetValue(24).ToString() + "','" + li.Row.ItemArray.GetValue(26).ToString() + "','" + li.Row.ItemArray.GetValue(27).ToString() + "','" + li.Row.ItemArray.GetValue(28).ToString() + "','" + li.Row.ItemArray.GetValue(29).ToString() + "','" + li.Row.ItemArray.GetValue(30).ToString() + "','" + li.Row.ItemArray.GetValue(31).ToString() + "','" + li.Row.ItemArray.GetValue(32).ToString() + "','" + li.Row.ItemArray.GetValue(33).ToString() + "','" + li.Row.ItemArray.GetValue(35).ToString() + "','" + li.Row.ItemArray.GetValue(36).ToString() + "','" + li.Row.ItemArray.GetValue(37).ToString() + "','" + li.Row.ItemArray.GetValue(38).ToString() + "','" + li.Row.ItemArray.GetValue(39).ToString() + "','" + li.Row.ItemArray.GetValue(40).ToString() + "','" + li.Row.ItemArray.GetValue(41).ToString() + "')";
                dbc.sqlExecute(sql);
                dbc.sqlExecute("delete from Archyvas$ where id=" + idd);
            }
            DataTable tb = dbc.FindStringInTableArch(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        public static void DeleteFromDB(DataGrid dataGrid1, string searchword)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            for (int i = 0; i < dataGrid1.SelectedItems.Count; i++)
            {
                DataRowView li = (DataRowView) dataGrid1.SelectedItems[i];
                dbc.sqlExecute("delete from Archyvas$ where id=" + li.Row.ItemArray.GetValue(0x22).ToString());
            }
            DataTable tb = dbc.FindStringInTable(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        public static void getData(DataGrid dataGrid1)
        {
            fromrec = 0;
            countrec = 100;
            DataTable tb = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem").getTable("select * from Archyvas$ order by Kompanija,[Pavadinimas originalo kalba] asc");
            dataGrid1.DataContext = tb;
        }

        public static void getDataArch(DataGrid dataGrid1)
        {
            fromrec = 0;
            countrec = 100;
            DataTable tb = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem").getTable("select top 50 * from Archyvas$Archyve order by Kompanija,[Pavadinimas originalo kalba] asc");
            dataGrid1.DataContext = tb;
        }

        public static bool insertIntoDB(Window2 iw)
        {
            bool commited = false;
            string sql;
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            if (!iw.textBox33.Text.Equals(""))
            {
                if (int.Parse(iw.textBox33.Text) > 0)
                {
                    for (int i = 0; i < int.Parse(iw.textBox33.Text); i++)
                    {
                        string originname = "";
                        if (iw.textBox2.Text.Contains("''"))
                        {
                            originname = iw.textBox2.Text;
                        }
                        else { originname = iw.textBox2.Text.Replace("'", "''"); }
                        string sq = "select * from Archyvas$ where Sezonas='" + iw.textBox32.Text + "' and Serija='" + (i + 1) + "' and [Pavadinimas originalo kalba] Like '%" + originname + "%'";
                        if (dbc.getTable(sq).Rows.Count == 0)
                        {
                            string sqll = "";
                            sqll = sqll + "insert into Archyvas$ ([Kompanija],[Pavadinimas originalo kalba],[Lietuviškas pavadinimas],[Trukmė 1],[Trukmė 2],[TC trukmė],[Pradzios TC],[I Įsikirtimas],[II Įsikirtimas] ,[III Įsikirtimas],[IV Įsikirtimas],[V Įsikirtimas],[VI Įsikirtimas],[VII Įsikirtimas],[VIII Įsikirtimas],[IX Įsikirtimas],[X Įsikirtimas],[XI Įsikirtimas],[Eteris 1],[Eteris 2],[Eteris 3],[Eteris 4],[Eteris 5] ,[P raidė],[Intarpai bėg#eil],[Licencijos pradžia],[Licencijos pabaiga],žanras,Metai,Režisierius,Aktoriai,Sezonas,Serija,[XII Įsikirtimas],[XIII Įsikirtimas],[XIV Įsikirtimas],[XV Įsikirtimas],KasetesNr,Salis,indeksas) values ('";
                            string kompanija = "";
                            if (iw.textBox1.Text.Contains("''"))
                            {
                                kompanija = iw.textBox1.Text.Replace("'", "''");
                            }
                            else { kompanija = iw.textBox1.Text.Replace("'", "''"); }

                            sqll = sqll + kompanija;
                            sqll = sqll + "','";
                            
                            sqll = sqll + originname;
                            sqll = sqll + " ";
                            if (!iw.textBox32.Text.Equals(""))
                            {
                                sqll = sqll + iw.textBox32.Text.Replace("'", "''");
                                sqll = sqll + "/";
                            }
                            sqll = sqll + (i + 1).ToString();
                            sqll = sqll + "','";

                            string name = "";
                            if (iw.textBox3.Text.Contains("''"))
                            {
                                name = iw.textBox3.Text.Replace("'", "''");
                            }
                            else { name = iw.textBox3.Text.Replace("'", "''"); }
                            sqll = sqll + name;
                            sqll = sqll + " ";
                            if (!iw.textBox32.Text.Equals(""))
                            {
                                sqll = sqll + iw.textBox32.Text.Replace("'", "''");
                                sqll = sqll + "/";
                            }
                            sqll = sqll + (i + 1).ToString();
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox4.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox5.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox6.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox7.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox8.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox9.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox10.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox11.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox12.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox13.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox14.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox15.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox16.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox17.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox18.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox19.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox20.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox21.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox22.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox23.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox24.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox25.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox26.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox27.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox28.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox29.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox30.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox31.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox32.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + (i + 1).ToString();
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox34.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox35.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox36.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox37.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox38.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox39.Text.Replace("'", "''");
                            sqll = sqll + "','";
                            sqll = sqll + iw.textBox40.Text.Replace("'", "''");
                            sqll = sqll + "')";
                            commited = dbc.sqlExecute(sqll);
                        }
                    }
                }
            }
            else
            {
                sql = "insert into Archyvas$ ([Kompanija],[Pavadinimas originalo kalba],[Lietuviškas pavadinimas],[Trukmė 1],[Trukmė 2],[TC trukmė],[Pradzios TC],[I Įsikirtimas],[II Įsikirtimas] ,[III Įsikirtimas],[IV Įsikirtimas],[V Įsikirtimas],[VI Įsikirtimas],[VII Įsikirtimas],[VIII Įsikirtimas],[IX Įsikirtimas],[X Įsikirtimas],[XI Įsikirtimas],[Eteris 1],[Eteris 2],[Eteris 3],[Eteris 4],[Eteris 5] ,[P raidė],[Intarpai bėg#eil],[Licencijos pradžia],[Licencijos pabaiga],žanras,Metai,Režisierius,Aktoriai,Sezonas,Serija,[XII Įsikirtimas],[XIII Įsikirtimas],[XIV Įsikirtimas],[XV Įsikirtimas],KasetesNr,Salis,indeksas) values ('" + iw.textBox1.Text.Replace("'", "''") + "','" + iw.textBox2.Text.Replace("'", "''") + "','" + iw.textBox3.Text.Replace("'", "''") + "','" + iw.textBox4.Text.Replace("'", "''") + "','" + iw.textBox5.Text.Replace("'", "''") + "','" + iw.textBox6.Text.Replace("'", "''") + "','" + iw.textBox7.Text.Replace("'", "''") + "','" + iw.textBox8.Text.Replace("'", "''") + "','" + iw.textBox9.Text.Replace("'", "''") + "','" + iw.textBox10.Text.Replace("'", "''") + "','" + iw.textBox11.Text.Replace("'", "''") + "','" + iw.textBox12.Text.Replace("'", "''") + "','" + iw.textBox13.Text.Replace("'", "''") + "','" + iw.textBox14.Text.Replace("'", "''") + "','" + iw.textBox15.Text.Replace("'", "''") + "','" + iw.textBox16.Text.Replace("'", "''") + "','" + iw.textBox17.Text.Replace("'", "''") + "','" + iw.textBox18.Text.Replace("'", "''") + "','" + iw.textBox19.Text.Replace("'", "''") + "','" + iw.textBox20.Text.Replace("'", "''") + "','" + iw.textBox21.Text.Replace("'", "''") + "','" + iw.textBox22.Text.Replace("'", "''") + "','" + iw.textBox23.Text.Replace("'", "''") + "','" + iw.textBox24.Text.Replace("'", "''") + "','" + iw.textBox25.Text.Replace("'", "''") + "','" + iw.textBox26.Text.Replace("'", "''") + "','" + iw.textBox27.Text.Replace("'", "''") + "','" + iw.textBox28.Text.Replace("'", "''") + "','" + iw.textBox29.Text.Replace("'", "''") + "','" + iw.textBox30.Text.Replace("'", "''") + "','" + iw.textBox31.Text.Replace("'", "''") + "','" + iw.textBox32.Text.Replace("'", "''") + "','" + iw.textBox33.Text.Replace("'", "''") + "','" + iw.textBox34.Text.Replace("'", "''") + "','" + iw.textBox35.Text.Replace("'", "''") + "','" + iw.textBox36.Text.Replace("'", "''") + "','" + iw.textBox37.Text.Replace("'", "''") + "','" + iw.textBox38.Text.Replace("'", "''") + "','" + iw.textBox39.Text.Replace("'", "''") + "','" + iw.textBox40.Text.Replace("'", "''") + "')";
                commited=dbc.sqlExecute(sql);
            }
            return commited;
        }

        public static void RestorefromArchive(DataGrid dataGrid1, string searchword)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            for (int i = 0; i < dataGrid1.SelectedItems.Count; i++)
            {
                DataRowView li = (DataRowView) dataGrid1.SelectedItems[i];
                string idd = li.Row.ItemArray.GetValue(0x22).ToString();
                string sql = "insert into Archyvas$ ([Kompanija],[Pavadinimas originalo kalba],[Lietuviškas pavadinimas],[Trukmė 1],[Trukmė 2],[TC trukmė],[Pradzios TC],[I Įsikirtimas],[II Įsikirtimas] ,[III Įsikirtimas],[IV Įsikirtimas],[V Įsikirtimas],[VI Įsikirtimas],[VII Įsikirtimas],[VIII Įsikirtimas],[IX Įsikirtimas],[X Įsikirtimas],[XI Įsikirtimas],[Eteris 1],[Eteris 2],[Eteris 3],[Eteris 4],[Eteris 5] ,[P raidė],[Intarpai bėg#eil],[Licencijos pradžia],[Licencijos pabaiga],žanras,Metai,Režisierius,Aktoriai,Sezonas,Serija,[XII Įsikirtimas],[XIII Įsikirtimas],[XIV Įsikirtimas],[XV Įsikirtimas],KasetesNr,Salis,indeksas) values ('" + li.Row.ItemArray.GetValue(0).ToString() + "','" + li.Row.ItemArray.GetValue(1).ToString() + "','" + li.Row.ItemArray.GetValue(2).ToString() + "','" + li.Row.ItemArray.GetValue(3).ToString() + "','" + li.Row.ItemArray.GetValue(4).ToString() + "','" + li.Row.ItemArray.GetValue(5).ToString() + "','" + li.Row.ItemArray.GetValue(6).ToString() + "','" + li.Row.ItemArray.GetValue(7).ToString() + "','" + li.Row.ItemArray.GetValue(8).ToString() + "','" + li.Row.ItemArray.GetValue(9).ToString() + "','" + li.Row.ItemArray.GetValue(10).ToString() + "','" + li.Row.ItemArray.GetValue(11).ToString() + "','" + li.Row.ItemArray.GetValue(12).ToString() + "','" + li.Row.ItemArray.GetValue(13).ToString() + "','" + li.Row.ItemArray.GetValue(14).ToString() + "','" + li.Row.ItemArray.GetValue(15).ToString() + "','" + li.Row.ItemArray.GetValue(16).ToString() + "','" + li.Row.ItemArray.GetValue(17).ToString() + "','" + li.Row.ItemArray.GetValue(18).ToString() + "','" + li.Row.ItemArray.GetValue(19).ToString() + "','" + li.Row.ItemArray.GetValue(20).ToString() + "','" + li.Row.ItemArray.GetValue(21).ToString() + "','" + li.Row.ItemArray.GetValue(22).ToString() + "','" + li.Row.ItemArray.GetValue(23).ToString() + "','" + li.Row.ItemArray.GetValue(24).ToString() + "','" + li.Row.ItemArray.GetValue(26).ToString() + "','" + li.Row.ItemArray.GetValue(27).ToString() + "','" + li.Row.ItemArray.GetValue(28).ToString() + "','" + li.Row.ItemArray.GetValue(29).ToString() + "','" + li.Row.ItemArray.GetValue(30).ToString() + "','" + li.Row.ItemArray.GetValue(31).ToString() + "','" + li.Row.ItemArray.GetValue(32).ToString() + "','" + li.Row.ItemArray.GetValue(33).ToString() + "','" + li.Row.ItemArray.GetValue(35).ToString() + "','" + li.Row.ItemArray.GetValue(36).ToString() + "','" + li.Row.ItemArray.GetValue(37).ToString() + "','" + li.Row.ItemArray.GetValue(38).ToString() + "','" + li.Row.ItemArray.GetValue(39).ToString() + "','" + li.Row.ItemArray.GetValue(40).ToString() + "','" + li.Row.ItemArray.GetValue(41).ToString() + "')";
                dbc.sqlExecute(sql);
                dbc.sqlExecute("delete from Archyvas$Archyve  where id=" + idd);
            }
            DataTable tb = dbc.FindStringInTableArch(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        public static void SearchInDB(DataGrid dataGrid1, string searchword)
        {
            DataTable tb = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem").FindStringInTable(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        public static void SearchInDBArch(DataGrid dataGrid1, string searchword)
        {
            DataTable tb = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem").FindStringInTableArch(searchword, fromrec, countrec);
            dataGrid1.DataContext = tb;
        }

        internal static void updateIntoDB(Window2 iw, DataGrid dataGrid1, string searchword, string id)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            DataTable tb = dbc.getTable("select * from Archyvas$ where id=" + id);
            iw.Title = "Įrašo redagavimas";
            iw.textBox1.Text = tb.Rows[0]["Kompanija"].ToString();
            iw.textBox2.Text = tb.Rows[0]["Pavadinimas originalo kalba"].ToString();
            iw.textBox3.Text = tb.Rows[0]["Lietuviškas pavadinimas"].ToString();
            iw.textBox4.Text = tb.Rows[0]["Trukmė 1"].ToString();
            iw.textBox5.Text = tb.Rows[0]["Trukmė 2"].ToString();
            iw.textBox6.Text = tb.Rows[0]["TC trukmė"].ToString();
            iw.textBox7.Text = tb.Rows[0]["Pradzios TC"].ToString();
            iw.textBox8.Text = tb.Rows[0]["I Įsikirtimas"].ToString();
            iw.textBox9.Text = tb.Rows[0]["II Įsikirtimas"].ToString();
            iw.textBox10.Text = tb.Rows[0]["III Įsikirtimas"].ToString();
            iw.textBox11.Text = tb.Rows[0]["IV Įsikirtimas"].ToString();
            iw.textBox12.Text = tb.Rows[0]["V Įsikirtimas"].ToString();
            iw.textBox13.Text = tb.Rows[0]["VI Įsikirtimas"].ToString();
            iw.textBox14.Text = tb.Rows[0]["VII Įsikirtimas"].ToString();
            iw.textBox15.Text = tb.Rows[0]["VIII Įsikirtimas"].ToString();
            iw.textBox16.Text = tb.Rows[0]["IX Įsikirtimas"].ToString();
            iw.textBox17.Text = tb.Rows[0]["X Įsikirtimas"].ToString();
            iw.textBox18.Text = tb.Rows[0]["XI Įsikirtimas"].ToString();
            iw.textBox19.Text = tb.Rows[0]["Eteris 1"].ToString();
            iw.textBox20.Text = tb.Rows[0]["Eteris 2"].ToString();
            iw.textBox21.Text = tb.Rows[0]["Eteris 3"].ToString();
            iw.textBox22.Text = tb.Rows[0]["Eteris 4"].ToString();
            iw.textBox23.Text = tb.Rows[0]["Eteris 5"].ToString();
            iw.textBox24.Text = tb.Rows[0]["P raidė"].ToString();
            iw.textBox25.Text = tb.Rows[0]["Intarpai bėg#eil"].ToString();
            iw.textBox26.Text = tb.Rows[0]["Licencijos pradžia"].ToString();
            iw.textBox27.Text = tb.Rows[0]["Licencijos pabaiga"].ToString();
            iw.textBox28.Text = tb.Rows[0]["žanras"].ToString();
            iw.textBox29.Text = tb.Rows[0]["Metai"].ToString();
            iw.textBox30.Text = tb.Rows[0]["Režisierius"].ToString();
            iw.textBox31.Text = tb.Rows[0]["Aktoriai"].ToString();
            iw.textBox32.Text = tb.Rows[0]["Sezonas"].ToString();
            iw.textBox33.Text = tb.Rows[0]["Serija"].ToString();
            iw.textBox34.Text = tb.Rows[0]["XII Įsikirtimas"].ToString();
            iw.textBox35.Text = tb.Rows[0]["XIII Įsikirtimas"].ToString();
            iw.textBox36.Text = tb.Rows[0]["XIV Įsikirtimas"].ToString();
            iw.textBox37.Text = tb.Rows[0]["XV Įsikirtimas"].ToString();
            iw.textBox38.Text = tb.Rows[0]["KasetesNr"].ToString();
            iw.textBox39.Text = tb.Rows[0]["Salis"].ToString();
            iw.textBox40.Text = tb.Rows[0]["indeksas"].ToString();
            if (iw.ShowDialog() == true)
            {
                if (iw.textBox2.Text.Equals(""))
                {
                    MessageBox.Show("Įveskite pavadinimà originalo kalba!");
                }
                else
                {
                    string kompanija = "";
                    if (iw.textBox1.Text.Contains("''"))
                    {
                        kompanija = iw.textBox1.Text;
                    }
                    else { kompanija = iw.textBox1.Text.Replace("'", "''"); }
                    string originname = "";
                    if (iw.textBox2.Text.Contains("''"))
                    {
                        originname = iw.textBox2.Text;
                    }
                    else { originname = iw.textBox2.Text.Replace("'", "''"); }
                    string name = "";
                    if (iw.textBox3.Text.Contains("''"))
                    {
                        name = iw.textBox3.Text;
                    }
                    else { name = iw.textBox3.Text.Replace("'", "''"); }
                    string rezisierius = "";
                    if (iw.textBox30.Text.Contains("''"))
                    {
                        rezisierius = iw.textBox30.Text;
                    }
                    else { rezisierius = iw.textBox30.Text.Replace("'", "''"); }
                    string aktoriai = "";
                    if (iw.textBox31.Text.Contains("''"))
                    {
                        aktoriai = iw.textBox31.Text;
                    }
                    else { aktoriai = iw.textBox31.Text.Replace("'", "''"); }
                    string sql = "UPDATE Archyvas$ SET [Kompanija] = '" + kompanija + "',[Pavadinimas originalo kalba] = '" + originname + "',[Lietuviškas pavadinimas] = '" + name + "',[Trukmė 1] = '" + iw.textBox4.Text + "',[Trukmė 2] = '" + iw.textBox5.Text + "',[TC trukmė] = '" + iw.textBox6.Text + "',[Pradzios TC] = '" + iw.textBox7.Text + "',[I Įsikirtimas] = '" + iw.textBox8.Text + "',[II Įsikirtimas] = '" + iw.textBox9.Text + "',[III Įsikirtimas] = '" + iw.textBox10.Text + "',[IV Įsikirtimas] = '" + iw.textBox11.Text + "',[V Įsikirtimas] = '" + iw.textBox12.Text + "',[VI Įsikirtimas] = '" + iw.textBox13.Text + "',[VII Įsikirtimas] = '" + iw.textBox14.Text + "',[VIII Įsikirtimas] = '" + iw.textBox15.Text + "',[IX Įsikirtimas] = '" + iw.textBox16.Text + "',[X Įsikirtimas] = '" + iw.textBox17.Text + "',[XI Įsikirtimas] = '" + iw.textBox18.Text + "',[Eteris 1] = '" + iw.textBox19.Text + "',[Eteris 2] = '" + iw.textBox20.Text + "',[Eteris 3] = '" + iw.textBox21.Text + "',[Eteris 4] = '" + iw.textBox22.Text + "',[Eteris 5] = '" + iw.textBox23.Text + "',[P raidė] = '" + iw.textBox24.Text + "',[Intarpai bėg#eil] = '" + iw.textBox25.Text + "',[Licencijos pradžia] = '" + iw.textBox26.Text + "',[Licencijos pabaiga] = '" + iw.textBox27.Text + "',žanras='" + iw.textBox28.Text + "',Metai='" + iw.textBox29.Text + "',Režisierius='" + rezisierius + "',Aktoriai='" + aktoriai + "',Sezonas='" + iw.textBox32.Text + "',Serija='" + iw.textBox33.Text + "',[XII Įsikirtimas] = '" + iw.textBox34.Text + "',[XIII Įsikirtimas] = '" + iw.textBox35.Text + "',[XIV Įsikirtimas] = '" + iw.textBox36.Text + "',[XV Įsikirtimas] = '" + iw.textBox37.Text + "',[KasetesNr] = '" + iw.textBox38.Text + "', Salis = '" + iw.textBox39.Text + "', indeksas = '" + iw.textBox40.Text + "' WHERE id=" + id;
                    dbc.sqlExecute(sql);
                    tb = dbc.FindStringInTable(searchword, fromrec, countrec);
                    dataGrid1.DataContext = tb;
                }
            }
        }

        public static void LoadAppointmentsByDate(DateTime StartDate, DateTime EndDate)
        {
            EndDate += TimeSpan.FromDays(1);
            EndDate = new DateTime(EndDate.Year, EndDate.Month, EndDate.Day, 0, 0, 0);
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            DataTable tb = dbc.getTable("select * from Programa where StartDate>='" + StartDate + "' and EndDate<'" + EndDate + "' order by StartDate");
            appointments.Clear();
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                Appointment ap = new Appointment();
                ap.Subject=tb.Rows[i]["Subject"].ToString();
                ap.StartTime=(DateTime)tb.Rows[i]["StartDate"];
                ap.EndTime = (DateTime)tb.Rows[i]["EndDate"];
                ap.Location = tb.Rows[i]["subID"].ToString();
                appointments.Add(ap);
            }
            try
                {
                    for (int y = 0; y <= appointments.Count; y++)
                    {
                        for (int i = appointments.Count-1; i >= 0; i--)
                        {
                            try
                            {
                                Appointment lastAp = appointments[i];
                                Appointment beforelastAp = appointments[i+1];
                                if ((lastAp.StartTime.Hour < 6) && (beforelastAp.StartTime.Hour >= 6) && lastAp.StartTime.ToShortDateString().Equals(beforelastAp.StartTime.ToShortDateString()))
                                {
                                    Appointment tmp = appointments[i];
                                    appointments[i] = appointments[i + 1];
                                    appointments[i + 1] = tmp;
                                }
                            }
                            catch (Exception ex) { }

                        }
                    }
                }
            catch (Exception ex) { }
        }

        public static void addAppointmentToDB(Appointment ap)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            string sql = "insert into Programa (Subject,StartDate,EndDate,subID) values ('" + ap.Subject.Replace("\r\n", "") + "','" + ap.StartTime + "','" + ap.EndTime + "','" + ap.Location + "')";
            dbc.sqlExecute(sql);
        }
        public static void removeAppointmentFromDB(Appointment ap)
        {
            DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
            string sub = ap.Subject.Replace("\r\n", "");
            string sqlc = "delete from Programa where StartDate='" + ap.StartTime + "' and EndDate='" + ap.EndTime + "'";
           // string sqlc = "delete from Programa where Subject='" + sub + "' and StartDate='" + ap.StartTime + "' and EndDate='" + ap.EndTime + "'";
            dbc.sqlExecute(sqlc);
        }

        public static DateTime GetFirstDateOfWeek(DateTime dayInWeek, DayOfWeek firstDay)
        {
            DateTime firstDayInWeek = dayInWeek.Date;
            while (firstDayInWeek.DayOfWeek != firstDay)
                firstDayInWeek = firstDayInWeek.AddDays(-1);

            return firstDayInWeek;
        }
        public static DateTime GetLastDateOfWeek(DateTime dayInWeek, DayOfWeek firstDay)
        {
            DateTime lastDayInWeek = dayInWeek.Date;
            while (lastDayInWeek.DayOfWeek != firstDay)
                lastDayInWeek = lastDayInWeek.AddDays(1);

            lastDayInWeek -= TimeSpan.FromDays(1);
            return lastDayInWeek;
        }

        public static DataTable getDataFromArchToCombo()
        {
             DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
             DataTable tb = dbc.getTable("select [Pavadinimas originalo kalba],[Lietuviškas pavadinimas],id from Archyvas$ where ([Pavadinimas originalo kalba] is not NULL and [Lietuviškas pavadinimas] is not NULL) and [Lietuviškas pavadinimas] <>'' order by dbo.fFilterNumeric([Lietuviškas pavadinimas])");
            return tb;
        }

        public static string removeNr(string str)
        {
            string rez =str;
            int ln = str.Length - 1;
            int cnt = 0;
            for (int i = ln; i > 0; i--)
            {
                try{
                    if (str.Substring(i, 1).Equals("/"))
                    {
                        rez = str.Substring(0, i);
                        ln--;
                        cnt++;
                    }else if ((int.Parse(str.Substring(i, 1)) >= 0) && (int.Parse(str.Substring(i, 1)) <= 9))
                    {
                    rez = str.Substring(0, i);
                    ln--;
                    cnt++;
                }
                }catch(Exception ex){ break;}
            }
            if (cnt>0){
                rez = rez.Substring(0,rez.Length - 1);
            }
            return rez;
        }

        public static string getProgramDateFromDB(string id,string Subject)
        {
            string result = "";
            try
            {
                if (!id.Equals("") && !id.Equals("null"))
                {
                    DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
                    DataTable tb = dbc.getTable("select * from Archyvas$ where id=" + id);

                    string priedas = "";
                    if (Subject.Contains("(tęs.)"))
                    {
                        priedas = " (tęs.)";
                    }
                    if (Subject.Contains("(kart.)"))
                    {
                        priedas = " (kart.)";
                    }
                    if (!tb.Rows[0]["Žanras"].ToString().Equals(""))
                    {
                        if (tb.Rows[0]["Lietuviškas pavadinimas"].ToString().Equals(tb.Rows[0]["Pavadinimas originalo kalba"].ToString()))
                        {
                            result += " " + removeNr(tb.Rows[0]["Lietuviškas pavadinimas"].ToString()) + priedas + ". " + tb.Rows[0]["Žanras"].ToString();
                        }
                        else
                        {
                            string skirt = ".";
                            if (tb.Rows[0]["Žanras"].ToString().Substring(tb.Rows[0]["Žanras"].ToString().Length - 1).Contains("."))
                                skirt = "";
                            string pav = removeNr(tb.Rows[0]["Lietuviškas pavadinimas"].ToString());
                            if (pav.Contains("("))
                            {
                                pav = pav.Replace(" (", "“ (");
                            }
                            else
                            {
                                pav = pav + "“";
                            }
                            result += " " + "„" + pav + priedas + ". " + tb.Rows[0]["Žanras"].ToString() + skirt;

                        }
                    }
                    else
                    {
                        result += " " + removeNr(tb.Rows[0]["Lietuviškas pavadinimas"].ToString()) + priedas;
                    }
                    if (!tb.Rows[0]["Režisierius"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["Režisierius"].ToString();
                    }
                    if (!tb.Rows[0]["Aktoriai"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["Aktoriai"].ToString();
                    }
                    if (!tb.Rows[0]["Salis"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["Salis"].ToString();
                    }
                    if (!tb.Rows[0]["Metai"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["Metai"].ToString();
                    }
                    if (!tb.Rows[0]["indeksas"].ToString().Equals(""))
                    {
                        result += ". " + tb.Rows[0]["indeksas"].ToString().Replace("(", "").Replace(")", "");
                    }
                    result = result.Replace("..", ".");
                }
            }
            catch (Exception ex) { }
            return result;
        }


        public static string getTinklelismDateFromDB(string id,string Subject)
        {
            string result = "";
            if (!id.Equals("") && !id.Equals("null"))
            {
                string priedas = "";
                if (Subject.Contains("(tęs.)"))
                {
                    priedas = " (tęs.)";
                }
                if (Subject.Contains("(kart.)"))
                {
                    priedas = " (kart.)";
                }
                DataBase dbc = new DataBase(@"192.168.100.100\SQLEXPRESS", "sa", "tornado", "PASystem");
                DataTable tb = dbc.getTable("select * from Archyvas$ where id=" + id);
               
                    if (!tb.Rows[0]["Lietuviškas pavadinimas"].ToString().Equals(tb.Rows[0]["Pavadinimas originalo kalba"].ToString()))
                    {
                        result = tb.Rows[0]["Lietuviškas pavadinimas"].ToString() + priedas+ ".   " + tb.Rows[0]["Pavadinimas originalo kalba"].ToString() + ". " + tb.Rows[0]["Kompanija"].ToString();
                    }
                    else
                    {
                        result = tb.Rows[0]["Pavadinimas originalo kalba"].ToString()+priedas;
                    }
                
                
            }
            return result;
        }

        public static IEnumerable<T> FindVisualChildren<T>(DependencyObject depObj) where T : DependencyObject
        {
            if (depObj != null)
            {
                for (int i = 0; i < VisualTreeHelper.GetChildrenCount(depObj); i++)
                {
                    DependencyObject child = VisualTreeHelper.GetChild(depObj, i);
                    if (child != null && child is T)
                    {
                        yield return (T)child;
                    }

                    foreach (T childOfChild in FindVisualChildren<T>(child))
                    {
                        yield return childOfChild;
                    }
                }
            }
        }
    
    
    }
}*/

